package com.adv.crm.services.impl;

import com.adv.crm.beans.AccountBean;
import com.adv.crm.beans.AccountQueryBean;
import com.adv.crm.dao.AccountDao;
import com.adv.crm.services.ExcelService;
import org.apache.commons.lang3.math.NumberUtils;
import org.apache.commons.lang3.time.DateFormatUtils;
import org.apache.commons.lang3.time.DateUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.io.IOException;
import java.io.InputStream;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

/**
 * Created by 16978 on 2016/6/18.
 */
@Service
public class ExcelServiceImpl implements ExcelService {
    @Autowired
    private AccountDao accountDao;

    public static void main(String[] args) throws IOException {
        ExcelServiceImpl service = new ExcelServiceImpl();
        //service.readExcel("d:\\temp\\weixin.xlsx");
    }

    public void readExcel(InputStream is) throws IOException {

        try {

            XSSFWorkbook wb = readFile(is);
            System.out.println("Data dump:\n");
            List<AccountBean> accounts = new ArrayList<AccountBean>();
//            for (int k = 0; k < wb.getNumberOfSheets(); k++) {
            XSSFSheet sheet = wb.getSheetAt(0);
            int rows = sheet.getPhysicalNumberOfRows();
            System.out.println("Sheet " + 0 + " \"" + wb.getSheetName(0) + "\" has " + rows + " row(s).");
            for (int r = 1; r < rows; r++) {
                XSSFRow row = sheet.getRow(r);
                if (row == null) {
                    continue;
                }

                int cells = row.getPhysicalNumberOfCells();

                //读取cell的值
                AccountBean accountBean = new AccountBean();

                String accountName = getStringCellValue(row.getCell(0));
                Double fansNum = NumberUtils.toDouble(getStringCellValue(row.getCell(1)), 0);
                String accountId = getStringCellValue(row.getCell(2));
                Double firstCost = NumberUtils.toDouble(getStringCellValue(row.getCell(3)), 0.0);
                Double secondCost = NumberUtils.toDouble(getStringCellValue(row.getCell(4)) + "", 0.0);
                String accountType = getStringCellValue(row.getCell(5));
                String fansQuality = getStringCellValue(row.getCell(6));
                String secondSupplier = getStringCellValue(row.getCell(8));
                String firstSupplier = getStringCellValue(row.getCell(9));
                Double discount = NumberUtils.toDouble(getStringCellValue(row.getCell(10)), 1);
                String remark = getStringCellValue(row.getCell(11));
                Double temp = NumberUtils.toDouble(getStringCellValue(row.getCell(7)));
                Date validDate = new Date();
                if (temp.intValue() > 0){
                    try {
                        validDate = DateUtils.parseDate(temp.intValue()+"", "yyMM");
                    } catch (ParseException e) {
                        e.printStackTrace();
                    }
                }
                accountBean.setAccountId(accountId);
                accountBean.setAccountName(accountName);
                accountBean.setFirstCost(firstCost);
                accountBean.setSecondCost(secondCost);
                accountBean.setFansNum(fansNum);
                accountBean.setFansQuality(fansQuality);
                accountBean.setFirstSupplier(firstSupplier);
                accountBean.setSecondSupplier(secondSupplier);
                accountBean.setAccountType(accountType);
                accountBean.setRemark(remark);
                accountBean.setValidDate(validDate);
                accountBean.setDiscount(discount);

                accounts.add(accountBean);

                if ((r > 0 && r % 500 == 0) || r == (rows - 1)) {
                    int result = accountDao.insertAccountByBatch(accounts);
                    if (result > 0) {
                        accounts = new ArrayList<AccountBean>();
                    }
                }

            }
            wb.close();
//            } else if (args.length == 2) {
//                if (args[1].toLowerCase().equals("write")) {
//                    System.out.println("Write mode");
//                    long time = System.currentTimeMillis();
//                    testCreateSampleSheet(fileName);
//
//                    System.out.println("" + (System.currentTimeMillis() - time)
//                            + " ms generation time");
//                } else {
//                    System.out.println("readwrite test");
//                    HSSFWorkbook wb = readFile(fileName);
//                    FileOutputStream stream = new FileOutputStream(args[1]);
//
//                    wb.write(stream);
//                    stream.close();
//                    wb.close();
//                }
//            } else if (args.length == 3 && args[2].toLowerCase(Locale.ROOT).equals("modify1")) {
//                // delete row 0-24, row 74 - 99 && change cell 3 on row 39 to string "MODIFIED CELL!!"
//
//                HSSFWorkbook wb = readFile(fileName);
//                FileOutputStream stream = new FileOutputStream(args[1]);
//                HSSFSheet sheet = wb.getSheetAt(0);
//
//                for (int k = 0; k < 25; k++) {
//                    HSSFRow row = sheet.getRow(k);
//
//                    sheet.removeRow(row);
//                }
//                for (int k = 74; k < 100; k++) {
//                    HSSFRow row = sheet.getRow(k);
//
//                    sheet.removeRow(row);
//                }
//                HSSFRow row = sheet.getRow(39);
//                HSSFCell cell = row.getCell(3);
//                cell.setCellValue("MODIFIED CELL!!!!!");
//
//                wb.write(stream);
//                stream.close();
//                wb.close();
//            }
        } catch (
                Exception e
                )

        {
            e.printStackTrace();
        }

    }

    private XSSFWorkbook readFile(InputStream is) throws IOException {
        try {
            return new XSSFWorkbook(is);
        } finally {
            is.close();
        }
    }

    @Override
    public XSSFWorkbook exportDataToExcel(AccountQueryBean queryBean) throws IOException {
        List<AccountBean> accounts = null;
        List<String> diff = null;
        boolean queryById = false;
        boolean queryByName = false;
        if (queryBean.getSearchType() == 1 && queryBean.getSearchField() == 0) {
            accounts = accountDao.queryAccountsByAccountIdList(queryBean);
            diff = filterResult(accounts, queryBean);
            queryById = true;
        } else if (queryBean.getSearchType() == 1 && queryBean.getSearchField() == 1) {
            accounts = accountDao.queryAccountsByAccountNameList(queryBean);
            diff = filterResult(accounts, queryBean);
            queryByName = true;
        } else {
            accounts = accountDao.queryAccounts(queryBean);
        }

        XSSFWorkbook wb = new XSSFWorkbook();
        XSSFSheet s = wb.createSheet();
        wb.setSheetName(0, "报价");

        XSSFFont boldFont = wb.createFont();
        boldFont.setFontName("微软雅黑");
        boldFont.setBold(true);

        XSSFFont font = wb.createFont();
        font.setFontName("微软雅黑");

        XSSFCellStyle greyStyle = wb.createCellStyle();
        greyStyle.setFont(boldFont);
        greyStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        greyStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        greyStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);

        XSSFCellStyle orangeStyle = wb.createCellStyle();
        orangeStyle.setFont(boldFont);
        orangeStyle.setFillForegroundColor(IndexedColors.LIGHT_ORANGE.getIndex());
        orangeStyle.setFillPattern(CellStyle.SOLID_FOREGROUND);
        orangeStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);

        XSSFCellStyle commonStyle = wb.createCellStyle();
        commonStyle.setFont(font);
        commonStyle.setAlignment(XSSFCellStyle.ALIGN_CENTER);

        XSSFRow head = s.createRow(0);
        XSSFCell cell0 = head.createCell(0);
        cell0.setCellValue("账号");
        cell0.setCellStyle(greyStyle);

        XSSFCell cell1 = head.createCell(1);
        cell1.setCellValue("粉丝（万）");
        cell1.setCellStyle(greyStyle);
        XSSFCell cell2 = head.createCell(2);
        cell2.setCellValue("ID");
        cell2.setCellStyle(greyStyle);
        XSSFCell cell3 = head.createCell(3);
        cell3.setCellValue("头条成本");
        cell3.setCellStyle(orangeStyle);
        XSSFCell cell4 = head.createCell(4);
        cell4.setCellValue("次条成本");
        cell4.setCellStyle(orangeStyle);
        XSSFCell cell5 = head.createCell(5);
        cell5.setCellValue("头条报价");
        cell5.setCellStyle(greyStyle);
        XSSFCell cell6 = head.createCell(6);
        cell6.setCellValue("次条报价");
        cell6.setCellStyle(greyStyle);
        XSSFCell cell7 = head.createCell(7);
        cell7.setCellValue("账号属性");
        cell7.setCellStyle(greyStyle);
        XSSFCell cell8 = head.createCell(8);
        cell8.setCellValue("粉丝质量");
        cell8.setCellStyle(greyStyle);
        XSSFCell cell9 = head.createCell(9);
        cell9.setCellValue("更新日期");
        cell9.setCellStyle(greyStyle);
        XSSFCell cell10 = head.createCell(10);
        cell10.setCellValue("供应商");
        cell10.setCellStyle(orangeStyle);
        XSSFCell cell11 = head.createCell(11);
        cell11.setCellValue("初始供应商");
        cell11.setCellStyle(orangeStyle);
        XSSFCell cell12 = head.createCell(12);
        cell12.setCellValue("优惠");
        cell12.setCellStyle(orangeStyle);
        XSSFCell cell13 = head.createCell(13);
        cell13.setCellValue("备注");
        cell13.setCellStyle(orangeStyle);

        double conditionPrice = queryBean.getConditionPrice();
        double value = queryBean.getValue();
        double coefficient = queryBean.getCoefficient();
        int rownum = 0;
        if (accounts != null && accounts.size() > 0) {
            for (; rownum < accounts.size(); rownum++) {
                AccountBean bean = accounts.get(rownum);
                XSSFRow r = s.createRow(rownum+1);

                XSSFCell c0 = r.createCell(0);
                c0.setCellStyle(commonStyle);
                c0.setCellValue(bean.getAccountName());

                XSSFCell c1 = r.createCell(1);
                c1.setCellValue(bean.getFansNum()==0?"保密":bean.getFansNum()+"");
                c1.setCellStyle(commonStyle);
                XSSFCell c2 = r.createCell(2);
                c2.setCellValue(bean.getAccountId());
                c2.setCellStyle(commonStyle);
                XSSFCell c3 = r.createCell(3);
                c3.setCellValue(bean.getFirstCost());
                c3.setCellStyle(commonStyle);
                XSSFCell c4 = r.createCell(4);
                c4.setCellValue(bean.getSecondCost());
                c4.setCellStyle(commonStyle);
                XSSFCell c7 = r.createCell(7);
                c7.setCellValue(bean.getAccountType());
                c7.setCellStyle(commonStyle);
                XSSFCell c8 = r.createCell(8);
                c8.setCellValue(bean.getFansQuality());
                c8.setCellStyle(commonStyle);
                XSSFCell c9 = r.createCell(9);
                c9.setCellValue(DateFormatUtils.format(bean.getValidDate(), "yyMM"));
                c9.setCellStyle(commonStyle);
                XSSFCell c10 = r.createCell(10);
                c10.setCellValue(bean.getSecondSupplier());
                c10.setCellStyle(commonStyle);
                XSSFCell c11 = r.createCell(11);
                c11.setCellValue(bean.getFirstSupplier());
                c11.setCellStyle(commonStyle);
                XSSFCell c12 = r.createCell(12);
                c12.setCellValue(bean.getDiscount());
                c12.setCellStyle(commonStyle);
                XSSFCell c13 = r.createCell(13);
                c13.setCellValue(bean.getRemark());
                c13.setCellStyle(commonStyle);


                double firstCost = bean.getFirstCost();
                double secondCost = bean.getSecondCost();
                double firstPrice = 0.0;
                double secondPrice = 0.0;

                if (firstCost < conditionPrice) {
                    firstPrice = firstCost + value;
                } else {
                    firstPrice = firstCost * coefficient;
                }

                if (secondCost < conditionPrice) {
                    secondPrice = secondCost + value;
                } else {
                    secondPrice = secondCost * coefficient;
                }

                XSSFCell c5 = r.createCell(5);
                c5.setCellValue(bean.getFirstPrice());
                c5.setCellStyle(commonStyle);
                XSSFCell c6 = r.createCell(6);
                c6.setCellValue(bean.getSecondPrice());
                c6.setCellStyle(commonStyle);


            }
        }

        if (diff != null) {
            for (int i = 0; i < diff.size(); i++) {
                XSSFRow r = s.createRow(rownum + 1);
                String str = diff.get(i);
                if (queryById) {
                    XSSFCell cid = r.createCell(2);
                    cid.setCellValue(str);
                    cid.setCellStyle(commonStyle);
                }
                if (queryByName) {
                    XSSFCell cname = r.createCell(0);
                    cname.setCellValue(str);
                    cname.setCellStyle(commonStyle);
                }
                rownum++;
            }
        }
        s.autoSizeColumn(0);
        s.autoSizeColumn(1);
        s.autoSizeColumn(2);
        s.autoSizeColumn(3);
        s.autoSizeColumn(4);
        s.autoSizeColumn(5);
        s.autoSizeColumn(6);
        s.autoSizeColumn(7);
        s.autoSizeColumn(8);
        s.autoSizeColumn(9);
        s.autoSizeColumn(10);
        s.autoSizeColumn(11);
        s.autoSizeColumn(12);
        s.autoSizeColumn(13);
        return  wb;
    }


    private String getStringCellValue(Cell cell) {
        String value = "";
        if (cell == null) {
            System.out.println("null");
            return "";
        }
        switch (cell.getCellType()) {

            case HSSFCell.CELL_TYPE_FORMULA:
                value = cell.getCellFormula();
                break;

            case HSSFCell.CELL_TYPE_NUMERIC:
                value += cell.getNumericCellValue();
                break;

            case HSSFCell.CELL_TYPE_STRING:
                value = cell.getStringCellValue();
                break;

            default:
        }
        return value;
    }

    private List<String> filterResult(List<AccountBean> accounts, AccountQueryBean bean) {
        List<String> ids = bean.getAccountIds();
        List<String> names = bean.getAccountNames();
        boolean checkId = false;
        boolean checkName = false;
        int accountSize = 0;
        if (accounts != null) {
            accountSize = accounts.size();
        }
        if (ids != null && ids.size() > 0 && accountSize < ids.size()) {
            checkId = true;
        } else if (names != null && names.size() > 0 && accountSize < names.size()) {
            checkName = true;
        }
        if (!checkId && !checkName) {
            return null;
        }
        List<String> temp = null;
        if (checkId) {
            temp = new ArrayList<String>(ids);
        } else if (checkName) {
            temp = new ArrayList<String>(names);
        } else {
            temp = new ArrayList<String>();
        }
        for (int i = 0; i < accounts.size(); i++) {
            AccountBean accountBean = accounts.get(i);
            if (checkId) {
                String accountId = accountBean.getAccountId();
                if (temp.contains(accountId)) {
                    temp.remove(accountId);
                }
            }
            if (checkName) {
                String accountName = accountBean.getAccountName();
                if (temp.contains(accountName)) {
                    temp.remove(accountName);
                }
            }
        }
        return temp;
    }
}
